------------------
-- 需求说明
------------------
-- 设计简易版的银行数据库表结构，要求可以完成以下基本功能
-- 银行开户（注册个人信息）及开卡（办理银行卡）（一个人可以办理多张银行卡，但最多办理三张）
-- 存钱
-- 查询余额
-- 取钱
-- 转账
-- 查看交易记录
-- 账户挂失
-- 账户注销

------------------
-- 表结构说明
------------------
-- 账户信息表
-- 银行卡表
-- 交易信息表（存储存钱和取钱的记录）
-- 转账信息表（存储转账信息记录）
-- 状态信息变化表（存储银行卡状态：1正常；2挂失；3冻结；4注销）

--------------------
-- 创建库
--------------------
create database Bank;
go

use Bank;
go



--------------------
-- 创建表
-------------------
-- 创建账号信息表
create table AccountInfo(
	AccountId int primary key identity(1, 1), -- 账户编号
	AccountCode varchar(20) not null, -- 身份证号码
	AccountPhone varchar(20) not null, -- 电话号码
	RealName varchar(20) not null, -- 真实姓名
	OpenTime smalldatetime not null, -- 开户时间
);
-- 创建银行表
create table BankCard(
	CardNo varchar(30) primary key, -- 银行卡号
	AccountId int not null,
	CardPwd varchar(30) not null, -- 银行密码
	CardBalance money not null default(0.00), -- 银行卡余额
	CardState tinyint not null default(1), -- 银行卡状态
	CardTime smalldatetime default(getdate()) -- 开卡时间
);
-- 创建交易信息表
create table CardExchange(
	ExchangeId int primary key identity(1, 1),
	CardNo varchar(30) not null, -- 银行卡号（与银行卡号形成主外键关系）
	MoneyInBank money not null, -- 存钱金额
	MoneyOutBank money not null, -- 取钱金额
	ExchangeTime smalldatetime not null, -- 交易时间 
);
-- 创建转账信息表
create table CardTransfer(
	TransferId int primary key identity(1, 1), -- 转账id
	CardNoOut varchar(30) not null, -- 转出银行卡号（与银行卡表形成主外键关系）
	CardNoIn varchar(30) not null, -- 转入银行卡号（与银行卡表形成主外键关系）
	TransferMoney money not null, -- 转账金额
	TransferTime smalldatetime not null, -- 转账时间
);
-- 创建状态信息变化表
create table CardStateChange(
	StateId int primary key identity(1, 1), -- 自增id 
	CardNo varchar(30) not null, -- 银行卡号（与银行卡表形成主外键关系）
	OldState tinyint not null, -- 银行卡原始状态
	NewState tinyint not null, -- 银行卡新状态
	StateWhy nvarchar(200) not null, -- 状态变化原因
	StateTime smalldatetime not null, -- 状态变化时间
);
go


--------------------
-- 为刘备，关羽，张飞执行开卡开户的操作
-------------------
-- 刘备身份证：420107198905064135
-- 关羽身份证：420107199507104133
-- 张飞身份证：420107199602034138
insert into AccountInfo (AccountCode, AccountPhone, RealName, OpenTime) 
	values ('420107198905064135', '13554785425', '刘备', GETDATE());
insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225125478544587', 1, '123456', 0, 1);

insert into AccountInfo (AccountCode, AccountPhone, RealName, OpenTime) 
	values ('420107199507104133', '13454788854', '关羽', GETDATE());
insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225547858741263', 2, '123456', 0, 1);

insert into AccountInfo (AccountCode, AccountPhone, RealName, OpenTime) 
	values ('420107199602034138', '13456896321', '张飞', GETDATE());

insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225547854125656', 3, '123456', 0, 1);
	
insert into AccountInfo (AccountCode, AccountPhone, RealName, OpenTime) 
	values ('420107199602015836', '13456894869', '赵云', GETDATE());

insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225547854148645', 4, '123456', 0, 1);
--------------------
-- 进行存钱操作，刘备存储2000元，关羽存钱8000元，张飞存钱50万。
-------------------
select * from BankCard left join AccountInfo on BankCard.AccountId = AccountInfo.AccountId;
select * from CardExchange;
update BankCard set CardBalance = CardBalance + 2000 where CardNo = '6225125478544587';
insert into CardExchange (CardNo, MoneyInBank, MoneyOutBank, ExchangeTime)
	values ('6225125478544587', 2000, 0, GETDATE());

update BankCard set CardBalance = CardBalance + 8000 where CardNo = '6225547858741263';
insert into CardExchange (CardNo, MoneyInBank, MoneyOutBank, ExchangeTime)
	values ('6225547858741263', 8000, 0, GETDATE());

update BankCard set CardBalance = CardBalance + 500000 where CardNo = '6225547854125656';
insert into CardExchange (CardNo, MoneyInBank, MoneyOutBank, ExchangeTime)
	values ('6225547854125656', 500000, 0, GETDATE());


--------------------
-- 进行转账操作，刘备给张飞转1000元
-------------------
select * from BankCard left join AccountInfo on BankCard.AccountId = AccountInfo.AccountId;
select * from CardTransfer;
update BankCard set CardBalance = CardBalance - 1000 where CardNo = '6225125478544587';
update BankCard set CardBalance = CardBalance + 1000 where CardNo = '6225547854125656';
insert into CardTransfer (CardNoOut, CardNoIn, TransferMoney, TransferTime) 
	values ('6225125478544587', '6225547854125656', 1000, GETDATE());

select * from AccountInfo;	
select * from BankCard;
select * from CardExchange;
select * from CardTransfer;
select * from CardStateChange
